from fastapi import APIRouter, HTTPException, Depends, Query
from sqlalchemy.orm import Session
from sqlalchemy import func, desc
from typing import List, Optional
from datetime import datetime
from database import get_db, DBProduct
from models import (  # 添加 ProductUpdate 导入
    ProductCreate, ProductResponse, ProductListResponse, 
    ProductStats, ProductQuery, APIResponse, ProductUpdate
) 
from auth import get_current_user, get_current_admin_user

router = APIRouter()

@router.get("/", response_model=ProductListResponse)
async def get_products(
    product_name: Optional[str] = Query(None, description="商品名称"),
    category: Optional[str] = Query(None, description="商品类别"),
    min_price: Optional[float] = Query(None, description="最低价格"),
    max_price: Optional[float] = Query(None, description="最高价格"),
    min_stock: Optional[int] = Query(None, description="最低库存"),
    max_stock: Optional[int] = Query(None, description="最高库存"),
    min_sales: Optional[int] = Query(None, description="最低销量"),
    max_sales: Optional[int] = Query(None, description="最高销量"),
    page: int = Query(1, ge=1, description="页码"),
    page_size: int = Query(20, ge=1, le=100, description="每页大小"),
    db: Session = Depends(get_db),
    current_user = Depends(get_current_user)
):
    """获取商品数据列表"""
    
    query = db.query(DBProduct)
    
    if product_name:
        query = query.filter(DBProduct.product_name.contains(product_name))
    
    if category:
        query = query.filter(DBProduct.category == category)
    
    if min_price is not None:
        query = query.filter(DBProduct.price >= min_price)
    
    if max_price is not None:
        query = query.filter(DBProduct.price <= max_price)
    
    if min_stock is not None:
        query = query.filter(DBProduct.stock >= min_stock)
    
    if max_stock is not None:
        query = query.filter(DBProduct.stock <= max_stock)
    
    if min_sales is not None:
        query = query.filter(DBProduct.sales >= min_sales)
    
    if max_sales is not None:
        query = query.filter(DBProduct.sales <= max_sales)
    
    # 获取总数
    total = query.count()
    
    # 分页和排序
    products = query.order_by(desc(DBProduct.sales), desc(DBProduct.id))\
                   .offset((page - 1) * page_size)\
                   .limit(page_size)\
                   .all()
    
    # 计算总页数
    total_pages = (total + page_size - 1) // page_size
    
    return ProductListResponse(
        data=[ProductResponse(
            id=item.id,
            product_name=item.product_name,
            price=item.price,
            category=item.category,
            stock=item.stock,
            sales=item.sales,
            created_at=item.created_at,
            updated_at=item.updated_at
        ) for item in products],
        total=total,
        page=page,
        page_size=page_size,
        total_pages=total_pages
    )

@router.post("/", response_model=ProductResponse)
async def create_product(
    product_data: ProductCreate,
    db: Session = Depends(get_db),
    current_user = Depends(get_current_admin_user)
):
    """添加商品数据"""
    
    db_product = DBProduct(
        product_name=product_data.product_name,
        price=product_data.price,
        category=product_data.category,
        stock=product_data.stock,
        sales=product_data.sales
    )
    
    db.add(db_product)
    db.commit()
    db.refresh(db_product)
    
    return ProductResponse(
        id=db_product.id,
        product_name=db_product.product_name,
        price=db_product.price,
        category=db_product.category,
        stock=db_product.stock,
        sales=db_product.sales,
        created_at=db_product.created_at,
        updated_at=db_product.updated_at
    )

@router.put("/{product_id}", response_model=ProductResponse)
async def update_product(
    product_id: int,
    product_data: ProductUpdate,
    db: Session = Depends(get_db),
    current_user = Depends(get_current_admin_user)
):
    """更新商品数据"""
    
    product = db.query(DBProduct).filter(DBProduct.id == product_id).first()
    
    if not product:
        raise HTTPException(status_code=404, detail="商品不存在")
    
    # 更新可修改的字段
    if product_data.product_name is not None:
        product.product_name = product_data.product_name
    if product_data.price is not None:
        product.price = product_data.price
    if product_data.category is not None:
        product.category = product_data.category
    if product_data.stock is not None:
        product.stock = product_data.stock
    if product_data.sales is not None:
        product.sales = product_data.sales
    
    product.updated_at = datetime.utcnow()
    
    db.commit()
    db.refresh(product)
    
    return ProductResponse(
        id=product.id,
        product_name=product.product_name,
        price=product.price,
        category=product.category,
        stock=product.stock,
        sales=product.sales,
        created_at=product.created_at,
        updated_at=product.updated_at
    )

@router.delete("/{product_id}", response_model=APIResponse)
async def delete_product(
    product_id: int,
    db: Session = Depends(get_db),
    current_user = Depends(get_current_admin_user)
):
    """删除商品数据"""
    
    product = db.query(DBProduct).filter(DBProduct.id == product_id).first()
    
    if not product:
        raise HTTPException(status_code=404, detail="商品不存在")
    
    db.delete(product)
    db.commit()
    
    return APIResponse(
        success=True,
        message="商品删除成功"
    )

@router.get("/categories", response_model=List[str])
async def get_categories(
    db: Session = Depends(get_db),
    current_user = Depends(get_current_user)
):
    """获取所有商品类别"""
    
    categories = db.query(DBProduct.category).distinct().all()
    return [category[0] for category in categories]

@router.get("/stats", response_model=ProductStats)
async def get_product_stats(
    db: Session = Depends(get_db),
    current_user = Depends(get_current_user)
):
    """获取商品数据统计信息"""
    
    # 总商品数
    total_products = db.query(DBProduct).count()
    
    # 商品类别数
    total_categories = db.query(DBProduct.category).distinct().count()
    
    # 平均价格
    avg_price_result = db.query(func.avg(DBProduct.price)).scalar()
    average_price = round(avg_price_result, 2) if avg_price_result else 0
    
    # 总库存
    total_stock = db.query(func.sum(DBProduct.stock)).scalar() or 0
    
    # 总销量
    total_sales = db.query(func.sum(DBProduct.sales)).scalar() or 0
    
    # 销量最高的5个商品
    top_selling = db.query(DBProduct).order_by(desc(DBProduct.sales)).limit(5).all()
    
    return ProductStats(
        total_products=total_products,
        total_categories=total_categories,
        average_price=average_price,
        total_stock=total_stock,
        total_sales=total_sales,
        top_selling=[ProductResponse(
            id=item.id,
            product_name=item.product_name,
            price=item.price,
            category=item.category,
            stock=item.stock,
            sales=item.sales,
            created_at=item.created_at,
            updated_at=item.updated_at
        ) for item in top_selling]
    )

@router.get("/stats/categories", response_model=List[dict])
async def get_category_stats(
    db: Session = Depends(get_db),
    current_user = Depends(get_current_user)
):
    """获取商品类别分布统计"""
    category_stats = db.query(
        DBProduct.category.label("name"),
        func.count(DBProduct.id).label("value")
    ).group_by(DBProduct.category).all()
    
    return [{"name": item.name, "value": item.value} for item in category_stats]

@router.get("/stats/prices", response_model=List[dict])
async def get_price_stats(
    db: Session = Depends(get_db),
    current_user = Depends(get_current_user)
):
    """获取商品价格分布统计"""
    # 定义价格区间
    price_ranges = [
        (0, 50),
        (50, 100),
        (100, 200),
        (200, 500),
        (500, 1000),
        (1000, None)
    ]
    
    results = []
    for min_price, max_price in price_ranges:
        query = db.query(DBProduct)
        
        if min_price is not None:
            query = query.filter(DBProduct.price >= min_price)
        
        if max_price is not None:
            query = query.filter(DBProduct.price < max_price)
        else:
            query = query.filter(DBProduct.price >= min_price)
        
        count = query.count()
        
        if max_price is not None:
            range_label = f"¥{min_price}-{max_price}"
        else:
            range_label = f"¥{min_price}+"
        
        results.append({
            "range": range_label,
            "count": count
        })
    
    return results